package demo06;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * 用PreparedStatement实现增删改查操作
 *
 * @author yingpeng
 *
 */
public class TestDemo {
	public static void main(String[] args) {
//		insert();
//		delete();
//		update();
		query();
	}
	//插入
	public static void insert(){
		//获取三个对象
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			//1.获取连接
			conn = JDBCUtils.getConnection();
			///2.创建SQL预处理对象(PreparedStatement)
			String sql = "insert into users (username,password) values(?,?)";
			pst = conn.prepareStatement(sql);
			//3.给pst中的sql?号占位符赋值
			pst.setObject(1, "前妻");
			pst.setObject(2, "3838");
			//4.执行语句
			int rows = pst.executeUpdate();
			System.out.println(rows);

		} catch (Exception e) {
			System.out.println(e);
		}finally {
			JDBCUtils.closeAll(conn, pst, null);;
		}
	}
	//删除
	public static void delete(){
		//获取三个对象
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			//1.获取连接
			conn = JDBCUtils.getConnection();
			//2.获取 预处理对象
			String sql = "delete from users where uid = ?";
			pst = conn.prepareStatement(sql);
			//3.给pst中的sql设置值
			pst.setInt(1, 5);
			//4.执行
			int rows = pst.executeUpdate();
			System.out.println(rows);

		} catch (Exception e) {
			// TODO: handle exception
		}finally {
			JDBCUtils.closeAll(conn, pst, null);
		}
	}
	//修改
	public static void update(){
		//获取三个对象
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			//1.获取连接
			conn = JDBCUtils.getConnection();
			//2.获取 预处理对象
			String sql  = "update users set password = ? where uid = ?";
			pst = conn.prepareStatement(sql);
			//3.设置值
			pst.setObject(1, "111");
			pst.setObject(2, "2");
			//4.执行
			int rows = pst.executeUpdate();
			System.out.println(rows);


		} catch (Exception e) {
			// TODO: handle exception
		}finally {
			JDBCUtils.closeAll(conn, pst, null);
		}
	}
	//查询
	public static void query(){
		//获取三个对象
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtils.getConnection();
			String sql = "select * from users where username like ?";
			pst = conn.prepareStatement(sql);
			pst.setObject(1, "%l%");
			rs = pst.executeQuery();
			//处理结果集
			while(rs.next()){
				Object uid = rs.getObject("uid");
				Object username = rs.getObject("username");
				Object password = rs.getObject("password");
				System.out.println(uid+"\t"+username+"\t"+password);
			}

		} catch (Exception e) {
			// TODO: handle exception
		}finally {
			JDBCUtils.closeAll(conn, pst, rs);
		}
	}
}
